package pgsql

import (
	"fmt"
	db_utils "gitee.com/zy_blue7/db-convert/db-utils"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
	"log"
)

type Pgsql struct {
	DriverName string
	UserName   string
	Password   string
	Host       string
	Port       string
	Schema     string
	DB         *sqlx.DB
}

func (p *Pgsql) GetDB() db_utils.DB {
	//TODO implement me
	panic("implement me")
}

func (m *Pgsql) GetSchema() string {
	return m.Schema
}

var _ db_utils.Databases = (*Pgsql)(nil)
var _ db_utils.Tables = (*Pgsql)(nil)
var _ db_utils.TableColumns = (*Pgsql)(nil)
var _ db_utils.PrimaryKeys = (*Pgsql)(nil)
var _ db_utils.ForeignKeys = (*Pgsql)(nil)
var _ db_utils.Indexes = (*Pgsql)(nil)
var _ db_utils.DataSchema = (*Pgsql)(nil)
var _ db_utils.DataConvert = (*Pgsql)(nil)

type Option func(*Pgsql)

func NewMysql(opts ...Option) *Pgsql {
	mysql := &Pgsql{
		DriverName: "",
		UserName:   "",
		Password:   "",
		Host:       "",
		Port:       "",
		Schema:     "",
	}
	for _, opt := range opts {
		opt(mysql)
	}
	mysql.DB = mysql.Conn()
	return mysql
}

func WithHost(host string) Option {
	return func(mysql *Pgsql) {
		mysql.Host = host
	}
}

func WithDriverName(DriverName string) Option {
	return func(mysql *Pgsql) {
		mysql.DriverName = DriverName
	}
}

func WithUserName(UserName string) Option {
	return func(mysql *Pgsql) {
		mysql.UserName = UserName
	}
}
func WithPassword(Password string) Option {
	return func(mysql *Pgsql) {
		mysql.Password = Password
	}
}
func WithPort(Port string) Option {
	return func(mysql *Pgsql) {
		mysql.Port = Port
	}
}
func WithSchema(Schema string) Option {
	return func(mysql *Pgsql) {
		mysql.Schema = Schema
	}
}

func (m *Pgsql) Conn() *sqlx.DB {
	// 连接数据库
	//db, err := sqlx.Connect("mysql", "bito:bitorobotics@tcp(127.0.0.1:33063)/mep_db_tmp?parseTime=true")
	db, err := sqlx.Connect(m.DriverName, fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true", m.UserName, m.Password, m.Host, m.Port, m.Schema))
	if err != nil {
		log.Fatalln(err)
	}
	return db
}

func (m *Pgsql) GetDatabases() ([]string, error) {
	var databases []string

	// MySQL 方式
	// err := m.DB.Select(&databases, "SHOW DATABASES")

	// PostgreSQL 方式
	err := m.DB.Select(&databases, "SELECT datname FROM pg_database WHERE datistemplate = false")

	// SQL Server 方式
	// err := db.Select(&databases, "SELECT name FROM sys.databases WHERE database_id > 4")

	if err != nil {
		return nil, fmt.Errorf("获取数据库列表失败: %w", err)
	}
	return databases, nil
}

func (m *Pgsql) GetTables(schema string) ([]string, error) {
	var tables []string

	// 通用方式 (information_schema)
	// query := "SELECT table_name FROM information_schema.tables WHERE table_schema = ?"
	// err := m.DB.Select(&tables, query, schema)

	// MySQL 专用方式
	//err := m.DB.Select(&tables, "SHOW TABLES")

	// todo 和 mysql 不一样这里的 schema 不是数据库名称，而是连接的数据库中的 命名空间，eg： public
	// PostgreSQL 专用方式
	err := m.DB.Select(&tables, "SELECT tablename FROM pg_tables WHERE schemaname = $1", schema)

	if err != nil {
		return nil, fmt.Errorf("获取表列表失败: %w", err)
	}
	return tables, nil
}

func (m *Pgsql) GetTableColumns(schema, tableName string) ([]db_utils.ColumnInfo, error) {
	var columns []db_utils.ColumnInfo

	// mysql 语法
	//query := `
	//    SELECT
	//        column_name,
	//        data_type,
	//        is_nullable,
	//        column_default,
	//        character_maximum_length,
	//        numeric_precision,
	//        numeric_scale,
	//        column_key,
	//        extra,
	//        column_comment
	//    FROM
	//        information_schema.columns
	//    WHERE
	//        table_schema = ? AND table_name = ?
	//    ORDER BY
	//        ordinal_position
	//`

	// pgsql 语法
	query := `
			SELECT
			   c.relname AS table_name,
	           a.attname AS column_name,
	           pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
	           NOT a.attnotnull AS is_nullable,
	           pg_catalog.pg_get_expr(ad.adbin, ad.adrelid) AS column_default,
	           CASE
	               WHEN a.atttypmod = -1 THEN NULL
	               WHEN t.typname IN ('varchar', 'bpchar') THEN a.atttypmod - 4
	               ELSE NULL
	           END AS character_maximum_length,
	           CASE
	               WHEN t.typname IN ('numeric', 'decimal') THEN (a.atttypmod - 4) >> 16
	               ELSE NULL
	           END AS numeric_precision,
	           CASE
	               WHEN t.typname IN ('numeric', 'decimal') THEN (a.atttypmod - 4) & 65535
	               ELSE NULL
	           END AS numeric_scale,
	           d.description column_comment
	       FROM
	           pg_attribute a
	       JOIN
	           pg_class c ON a.attrelid = c.oid
	       JOIN
	           pg_namespace n ON c.relnamespace = n.oid
	       JOIN
	           pg_type t ON a.atttypid = t.oid
	       LEFT JOIN
	           pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
	       LEFT JOIN
	           pg_description d ON a.attrelid = d.objoid AND a.attnum = d.objsubid
	       WHERE
	           n.nspname = ? AND c.relname =  ?
	           AND a.attnum > 0 AND NOT a.attisdropped
	       ORDER BY
	           table_name,a.attnum
	`

	err := m.DB.Select(&columns, query, schema, tableName)
	if err != nil {
		return nil, fmt.Errorf("获取表列信息失败: %w", err)
	}
	return columns, nil
}

func (m *Pgsql) GetAllTableColumns(schema string) ([]db_utils.ColumnInfo, error) {
	var columns []db_utils.ColumnInfo

	// mysql 语法
	//query := `
	//    SELECT
	//        column_name,
	//        data_type,
	//        is_nullable,
	//        column_default,
	//        character_maximum_length,
	//        numeric_precision,
	//        numeric_scale,
	//        column_key,
	//        extra,
	//        column_comment
	//    FROM
	//        information_schema.columns
	//    WHERE
	//        table_schema = ? AND table_name = ?
	//    ORDER BY
	//        ordinal_position
	//`

	// pgsql 语法
	query := `
			SELECT
			   c.relname AS table_name,
	           a.attname AS column_name,
	           pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
	           NOT a.attnotnull AS is_nullable,
	           pg_catalog.pg_get_expr(ad.adbin, ad.adrelid) AS column_default,
	           CASE
	               WHEN a.atttypmod = -1 THEN NULL
	               WHEN t.typname IN ('varchar', 'bpchar') THEN a.atttypmod - 4
	               ELSE NULL
	           END AS character_maximum_length,
	           CASE
	               WHEN t.typname IN ('numeric', 'decimal') THEN (a.atttypmod - 4) >> 16
	               ELSE NULL
	           END AS numeric_precision,
	           CASE
	               WHEN t.typname IN ('numeric', 'decimal') THEN (a.atttypmod - 4) & 65535
	               ELSE NULL
	           END AS numeric_scale,
	           d.description column_comment
	       FROM
	           pg_attribute a
	       JOIN
	           pg_class c ON a.attrelid = c.oid
	       JOIN
	           pg_namespace n ON c.relnamespace = n.oid
	       JOIN
	           pg_type t ON a.atttypid = t.oid
	       LEFT JOIN
	           pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
	       LEFT JOIN
	           pg_description d ON a.attrelid = d.objoid AND a.attnum = d.objsubid
	       WHERE
	           n.nspname = ? 
-- 	         AND c.relname =  ?
	           AND a.attnum > 0 AND NOT a.attisdropped
	       ORDER BY
	           table_name,a.attnum
	`

	err := m.DB.Select(&columns, query, schema)
	if err != nil {
		return nil, fmt.Errorf("获取表列信息失败: %w", err)
	}
	return columns, nil
}

func (m *Pgsql) GetPrimaryKeys(schema, tableName string) ([]db_utils.PrimaryKeyInfo, error) {
	var pks []db_utils.PrimaryKeyInfo

	query := `
        SELECT 
            tc.table_name,
            kcu.column_name,
            kcu.ordinal_position as key_seq
        FROM 
            information_schema.table_constraints tc
        JOIN 
            information_schema.key_column_usage kcu
            ON tc.constraint_name = kcu.constraint_name
            AND tc.table_schema = kcu.table_schema
        	AND tc.table_name = kcu.table_name
        WHERE 
            tc.constraint_type = 'PRIMARY KEY'
            AND tc.table_schema = ?
            AND tc.table_name = ?
        ORDER BY 
           tc.table_name, kcu.ordinal_position
    `

	err := m.DB.Select(&pks, query, schema, tableName)
	if err != nil {
		return nil, fmt.Errorf("获取主键信息失败: %w", err)
	}
	return pks, nil
}

func (m *Pgsql) GetAllPrimaryKeys(schema string) ([]db_utils.PrimaryKeyInfo, error) {
	var pks []db_utils.PrimaryKeyInfo

	query := `
        SELECT 
            tc.table_name,
            kcu.column_name,
            kcu.ordinal_position as key_seq
        FROM 
            information_schema.table_constraints tc
        JOIN 
            information_schema.key_column_usage kcu
            ON tc.constraint_name = kcu.constraint_name
            AND tc.table_schema = kcu.table_schema
        	AND tc.table_name = kcu.table_name
        WHERE 
            tc.constraint_type = 'PRIMARY KEY'
            AND tc.table_schema = ?
--             AND tc.table_name = ?
        ORDER BY 
            tc.table_name,kcu.ordinal_position
    `

	err := m.DB.Select(&pks, query, schema)
	if err != nil {
		return nil, fmt.Errorf("获取主键信息失败: %w", err)
	}
	return pks, nil
}

func (m *Pgsql) GetForeignKeys(schema, tableName string) ([]db_utils.ForeignKeyInfo, error) {
	var fks []db_utils.ForeignKeyInfo

	// mysql 语法
	//query := `
	//    SELECT
	//     distinct   tc.constraint_name,
	//        kcu.column_name,
	//        kcu.TABLE_NAME table_name,
	//        kcu.REFERENCED_TABLE_NAME AS foreign_table_name,
	//        kcu.REFERENCED_COLUMN_NAME AS foreign_column_name,
	//        rc.update_rule,
	//        rc.delete_rule
	//    FROM
	//        information_schema.table_constraints AS tc
	//    JOIN
	//        information_schema.key_column_usage AS kcu
	//        ON tc.constraint_name = kcu.constraint_name
	//        AND tc.table_schema = kcu.table_schema
	//    JOIN
	//        information_schema.REFERENTIAL_CONSTRAINTS AS ccu
	//        ON ccu.constraint_name = tc.constraint_name
	//    JOIN
	//        information_schema.referential_constraints AS rc
	//        ON rc.constraint_name = tc.constraint_name
	//    WHERE
	//        tc.constraint_type = 'FOREIGN KEY'
	//        AND tc.table_schema = ?
	//        AND tc.table_name = ?
	//`

	// pgsql 语法
	query := `
	SELECT
	       tc.constraint_name,
	       kcu.column_name,
	       kcu.table_name,
	       ccu.table_name AS foreign_table_name,
	       ccu.column_name AS foreign_column_name,
	       rc.update_rule,
	       rc.delete_rule
	   FROM
	       information_schema.table_constraints tc
	   JOIN
	       information_schema.key_column_usage kcu
	       ON tc.constraint_name = kcu.constraint_name
	       AND tc.table_schema = kcu.table_schema
	   JOIN
	       information_schema.constraint_column_usage ccu
	       ON ccu.constraint_name = tc.constraint_name
	   JOIN
	       information_schema.referential_constraints rc
	       ON rc.constraint_name = tc.constraint_name
	   WHERE
	       tc.constraint_type = 'FOREIGN KEY'
	       AND tc.table_schema = ?
	       AND tc.table_name = ?
	`

	err := m.DB.Select(&fks, query, schema, tableName)
	if err != nil {
		return nil, fmt.Errorf("获取外键信息失败: %w", err)
	}
	return fks, nil
}

func (m *Pgsql) GetAllForeignKeys(schema string) ([]db_utils.ForeignKeyInfo, error) {
	var fks []db_utils.ForeignKeyInfo

	// mysql 语法
	//query := `
	//    SELECT
	//     distinct   tc.constraint_name,
	//        kcu.column_name,
	//        kcu.TABLE_NAME table_name,
	//        kcu.REFERENCED_TABLE_NAME AS foreign_table_name,
	//        kcu.REFERENCED_COLUMN_NAME AS foreign_column_name,
	//        rc.update_rule,
	//        rc.delete_rule
	//    FROM
	//        information_schema.table_constraints AS tc
	//    JOIN
	//        information_schema.key_column_usage AS kcu
	//        ON tc.constraint_name = kcu.constraint_name
	//        AND tc.table_schema = kcu.table_schema
	//    JOIN
	//        information_schema.REFERENTIAL_CONSTRAINTS AS ccu
	//        ON ccu.constraint_name = tc.constraint_name
	//    JOIN
	//        information_schema.referential_constraints AS rc
	//        ON rc.constraint_name = tc.constraint_name
	//    WHERE
	//        tc.constraint_type = 'FOREIGN KEY'
	//        AND tc.table_schema = ?
	//        AND tc.table_name = ?
	//`

	// pgsql 语法
	query := `
	SELECT
	       tc.constraint_name,
	       kcu.column_name,
	       kcu.table_name,
	       ccu.table_name AS foreign_table_name,
	       ccu.column_name AS foreign_column_name,
	       rc.update_rule,
	       rc.delete_rule
	   FROM
	       information_schema.table_constraints tc
	   JOIN
	       information_schema.key_column_usage kcu
	       ON tc.constraint_name = kcu.constraint_name
	       AND tc.table_schema = kcu.table_schema
	   JOIN
	       information_schema.constraint_column_usage ccu
	       ON ccu.constraint_name = tc.constraint_name
	   JOIN
	       information_schema.referential_constraints rc
	       ON rc.constraint_name = tc.constraint_name
	   WHERE
	       tc.constraint_type = 'FOREIGN KEY'
	       AND tc.table_schema = ?
-- 	       AND tc.table_name = ?
	`

	err := m.DB.Select(&fks, query, schema)
	if err != nil {
		return nil, fmt.Errorf("获取外键信息失败: %w", err)
	}
	return fks, nil
}
func (m *Pgsql) GetIndexes(schema, tableName string) ([]db_utils.IndexInfo, error) {
	var indexes []db_utils.IndexInfo

	// mysql语法
	//query := `
	//    SELECT
	//        index_name,
	//        column_name,
	//        non_unique,
	//        index_type
	//    FROM
	//        information_schema.statistics
	//    WHERE
	//        table_schema = ?
	//        AND table_name = ?
	//    ORDER BY
	//        index_name, seq_in_index
	//`

	// pgsql 语法
	query := `
	SELECT distinct i.relname                                   AS indexname,
					a.attname                                   AS column_name,
					case when idx.indisunique then 1 else 0 end AS non_unique,
					am.amname                                      index_type,
					pg_get_indexdef(idx.indexrelid)             AS index_def
	FROM pg_class t,
		pg_class i,
		 pg_am am,
		pg_index idx,
		pg_attribute a,
		 pg_namespace n
	WHERE t.oid = idx.indrelid
		AND i.oid = idx.indexrelid
		AND a.attrelid = t.oid
		AND a.attnum = ANY (idx.indkey)
		AND t.relnamespace = n.oid
		AND n.nspname = ?
		AND t.relname = ?
	ORDER BY i.relname
	`

	err := m.DB.Select(&indexes, query, schema, tableName)
	if err != nil {
		return nil, fmt.Errorf("获取索引信息失败: %w", err)
	}
	return indexes, nil
}
func (m *Pgsql) GetAllIndexes(schema string) ([]db_utils.IndexInfo, error) {
	var indexes []db_utils.IndexInfo

	// mysql语法
	//query := `
	//    SELECT
	//        index_name,
	//        column_name,
	//        non_unique,
	//        index_type
	//    FROM
	//        information_schema.statistics
	//    WHERE
	//        table_schema = ?
	//        AND table_name = ?
	//    ORDER BY
	//        index_name, seq_in_index
	//`

	// pgsql 语法
	query := `
	SELECT distinct i.relname                                   AS indexname,
					a.attname                                   AS column_name,
					case when idx.indisunique then 1 else 0 end AS non_unique,
					am.amname                                      index_type,
					pg_get_indexdef(idx.indexrelid)             AS index_def
	FROM pg_class t,
		pg_class i,
		 pg_am am,
		pg_index idx,
		pg_attribute a,
		 pg_namespace n
	WHERE t.oid = idx.indrelid
		AND i.oid = idx.indexrelid
		AND a.attrelid = t.oid
		AND a.attnum = ANY (idx.indkey)
		AND t.relnamespace = n.oid
		AND n.nspname = ?
-- 		AND t.relname = ?
	ORDER BY i.relname
	`

	err := m.DB.Select(&indexes, query, schema)
	if err != nil {
		return nil, fmt.Errorf("获取索引信息失败: %w", err)
	}
	return indexes, nil
}

func (m *Pgsql) ConvertData(destDataSchema db_utils.DataSchema, config db_utils.ConvertConfig) error {
	//TODO implement me
	panic("implement me")
}
